#!/usr/bin/python
# coding:utf-8

import json
import os
from openpyxl import load_workbook
import threading
from settings import JSON_DIR, EXCEL_DIR, LANGUAGE, STR_TO_JSON_TYPE, NAME_EXPORT
from PyQt6.QtCore import QObject, pyqtSignal

from language import LNG

# import time

lng = LNG[LANGUAGE]
# 表格中的标题相关数据所占的行
TITLE_ROW = 3


# 定义信号
class Stream(QObject):
    newText = pyqtSignal(str)

    def emit(self, text):
        self.newText.emit(str(text))


class Translation(QObject):
    count = 0
    newText = pyqtSignal(str)

    def __init__(self, in_dir=EXCEL_DIR, out_dir=JSON_DIR):
        super().__init__()
        self.init_data(in_dir, out_dir)
        pass

    # 自定义打印方法
    def printf(self, value):
        self.newText.emit(value)
        # print(value)

    def init_data(self, in_dir, out_dir):
        """
        初始化外部数据
        """
        self.in_dir = in_dir
        self.out_dir = out_dir
        if not os.path.isdir(self.out_dir):
            os.mkdir(self.out_dir)
        pass

    # 获得excel文件
    def get_excels(self):
        # 遍历目录
        # 开始时，计算数量
        self.excel_count = 0
        self.json_count = 0
        # 初始化json数据变量
        self.json_data = {}
        for item in os.listdir(self.in_dir):
            # 如果是临时文件则忽略处理
            if '~' in item:
                continue
            # 仅处理excel格式的文件，其他的忽略
            if item.split('.')[1] in ['xls', 'xlsx']:
                self.excel_count += 1
                t = threading.Thread(target=self.handle, args=(item, ))
                t.start()
                # self.handle(item)
        pass

    # 字符串转数组
    def str2list(self, string, splitType):
        """
        字符串转数组，通过“|”分隔符进行分割
        """
        if isinstance(string, str):
            if len(string) < 1:
                return []
            str_list = string.split('|')
            # 按指定格式转换数据
            if splitType is not None:
                _list = []
                for i in str_list:
                    _string = i
                    _list.append(self.str2json(_string, splitType))
                return _list
            else:
                if len(str_list) > 1:
                    return str_list
        return string

    # 字符串转json格式
    def str2json(self, string, jsonType):
        """
        根据指定的分割样式将字符串转化为json格式，例如将"2_1"通过“id_value”的格式转化为{id:1,value:1}
        """
        j_list = jsonType.split('_')
        s_list = string.split('_')
        json_data = {}

        # 对条件字符串解析成json格式
        str_cond = STR_TO_JSON_TYPE
        if str_cond in j_list:
            _index = j_list.index(str_cond)
            try:
                _data = s_list[_index]
                _list = _data.split(':')
                _data = {}
                _data[_list[0]] = _list[1]
                s_list[_index] = _data
            except IndexError:
                # _data = ''
                s_list.append({})
            # s_list[_index] = _data

        for i in range(len(j_list)):
            try:
                data = s_list[i]
            except IndexError:
                data = {}
            json_data[j_list[i]] = data
        return json_data

    # 生成json格式下的kv数据或者数组数据
    def generateData(self, sheet, s_type):
        # if sheet.title not in ['Colors']:
        #     return {}
        # print(sheet.title)

        # time1 = time.perf_counter()
        # new_data = {}
        # for row in sheet.rows:
        #     row_tuple = tuple(row)
        #     if row_tuple[0].value == None:
        #         break
        #     # print(row_tuple[0])
        #     for cell in row:
        #         pass
        #         # if sheet.cell(1, cell.column).value == None:
        #         #     break
        #         # print(cell.value)

        # time2 = time.perf_counter()
        # print('aaa--{}'.format(time2-time1))
        # return {}

        nrows = sheet.max_row
        ncols = sheet.max_column
        # 初始化变量
        new_data = {} if s_type == 'j' else []
        # time1 = time.perf_counter()
        # time2 = time.perf_counter()
        # print('aaa--{}'.format(time2-time1))
        # time1 = time.perf_counter()
        for i in range(1, nrows + 1):
            # 会存在空行的数据，跳过该行数据的处理即可，不需要终止循环
            _value_i_1 = sheet.cell(i, 1).value
            if _value_i_1 is None:
                continue
            _value_i_1 = str(_value_i_1)
            # 不处理前几行的标题等数据
            if i < TITLE_ROW:
                continue
            _data = {}
            for j in range(1, ncols + 1):
                # 如果处理到该行最后的空列数据，跳出循环
                rol_title = sheet.cell(1, j).value
                stype_title = sheet.cell(2, j).value
                if rol_title is None:
                    break
                # 涉及到不需要处理的含“0”的数据，跳过
                if '0' in rol_title:
                    continue
                _cell_value = sheet.cell(i, j).value
                # sheet.cell(i, j).style = '常规'
                value = '' if _cell_value is None else _cell_value
                if value != '':
                    # 根据指定格式，格式化数据
                    if stype_title == 'str':
                        value = str(value)
                    elif stype_title == 'int':
                        value = int(value)
                    elif stype_title == 'float':
                        value = float(value)

                t_list = rol_title.split(':')
                if len(t_list) > 1:
                    _data[t_list[0]] = self.str2list(value, t_list[1])
                else:
                    _data[rol_title] = value

            if s_type == 'j':
                new_data[_value_i_1] = _data
            else:
                new_data.append(_data)

        # time2 = time.perf_counter()
        # print('aaa--{}'.format(time2-time1))
        return new_data
        pass

    # 转换表格数据
    def translateCell(self, ctype, cell):
        """
        转换表格数据，将excel中的数据类型转成json可以识别的类型
        还有其他待转换类型，目前并未给出
        0：empty（空的）；1：string（text）；2：number；3：date；4：boolean；5：error；6：blank（空白表格）
        """
        if ctype == 2 and cell % 1 == 0:
            cell = int(cell)
        return cell
        pass

    # 写入数据的方法
    def writeData(self, excel, data):
        """
        写入数据的方法
        """
        _name = excel.split('-')[2] if len(
            excel.split('_')) < 2 else excel.split('_')[1]
        _jsonName = _name.split('.')[0]
        self.json_data[_jsonName] = data
        self.json_count += 1
        if self.json_count == self.excel_count:
            # 写入文件
            with open(os.path.join(self.out_dir, NAME_EXPORT + '.json'),
                      'w',
                      encoding='utf-8') as f:
                json.dump(self.json_data, f, ensure_ascii=False)
                self.count += 1
                self.printf(lng[12].format(self.json_count, self.count))
            pass

    # 处理单个表格数据
    def handleSheet(self, excel, sheet, count):
        s_type = 'j' if 'id' in sheet['A1'].value.lower() else 'l'
        self.sheet_datas[sheet.title] = self.generateData(sheet, s_type)
        self.sheet_count += 1
        if self.sheet_count >= count:
            self.writeData(excel, self.sheet_datas)

        pass

    # excel转json
    def handle(self, excel):
        # 拿到excel数据
        # data_only表示可以将公式直接转换为计算结果，而不是显示一个公式
        # data = load_workbook(os.path.join(
        #     self.in_dir, excel), data_only=True, read_only=True)
        data = load_workbook(os.path.join(self.in_dir, excel), data_only=True)
        names = data.sheetnames
        # 判断数据表结构，单表直接转，多表按表名转换
        if len(names) > 1:
            # excel中多个表单的总数据
            self.sheet_datas = {}
            # 转化表单数量初始值
            self.sheet_count = 0
            # 表单总数量
            _count = len(
                [n for n in names if n.lower() != 'index' and '0' not in n])
            for name in names:
                # 将文件中的索引表和不需要转出的表移除，不转json
                if name == 'Index' or '0' in name:
                    self.printf('不需要生成的表单---{}'.format(name))
                    continue
                # sheet = data[name]
                # s_type = 'j' if 'id' in sheet['A1'].value.lower() else 'l'
                # new_data[name] = self.generateData(sheet, s_type)
                # t = threading.Thread(
                #     target=self.handleSheet, args=(excel, data[name], _count, ))
                # t.start()
                print(f"当前正在处理的sheet表为：{name}")
                self.handleSheet(excel, data[name], _count)
            # self.writeData(excel, new_data)
        else:
            sheet = data.active
            s_type = 'j' if 'id' in sheet['A1'].value.lower() else 'l'
            self.writeData(excel, self.generateData(sheet, s_type))


if __name__ == "__main__":
    t = Translation()
    t.get_excels()
